clear all
cd "C:\Users\ot3\Documents\CRFertility\BirthData"

***first get birth totals for the South***
***1955-1967 hand-transcriptions***
import excel "C:\Users\ot3\Documents\CRFertility\BirthData\StateAgeRaceTranscriptions.xlsx", sheet("Sheet1") firstrow clear  //transcribed by me from print books at http://www.nber.org/vital-stats-books/
reshape long births0 births1, i(fipsst year)  j(magecat)
reshape long births, i(fipsst year magecat)  j(black)
save births_docs, replace



*1968-1975 from microdata 
use natl1968 
recode mrace(1=0) (nonmiss=1), gen(black) //black is actually non-white
drop if mage8==1 | mage8==8
rename mage8 magecat
replace magecat=magecat-1
collapse (count) births=mrace, by(stateres magecat black) //just counts birth for obs with valid maternal race. 
g year=1968
rename stateres nchs_state
replace birth=birth*2 //all 50% samples through 1971
save births_docs_fmicrodata, replace


foreach y of numlist 1969/1971 {
di `y'
use natl`y', clear
recode mrace(1=0) (nonmiss=1), gen(black)
drop if mage8==1 | mage8==8
rename mage8 magecat
replace magecat=magecat-1
collapse (count) births=mrace, by(stateres magecat black) //just counts birth for obs with valid maternal race. 
g year=`y'
rename stateres nchs_state
replace birth=birth*2 //all 50% samples through 1971 
append using births_docs_fmicrodata
save births_docs_fmicrodata, replace
}

foreach y of numlist 1972/1975 {
di `y'
use natl`y' , clear
recode mrace(1=0) (nonmiss=1), gen(black)
drop if mage8==1 | mage8==8
rename mage8 magecat
replace magecat=magecat-1
collapse (sum) births=recwt, by(stateres magecat black) //summing recwt accoutns for some 50% samples, some full counts
g year=`y'
rename stateres nchs_state
append using births_docs_fmicrodata
save births_docs_fmicrodata, replace
}

*merge on FIPS codes, append transcriptions and microdata 
destring nchs_state, replace 
merge m:1 nchs_state using state_name_nchs_fips_xwalk 
keep if _merge==3
drop _merge nchs_state
save births_docs_fmicrodata, replace

append using births_docs

*keep south, collapse to age-race-year levels
keep if inlist(fipsst, 1, 5, 12, 13,  22, 28, 37, 45, 47, 48, 51)
collapse (sum) births_south=births, by(magecat black year)

save birthsbyage_south, replace
rm births_docs.dta
rm births_docs_fmicrodata.dta




*Now do again for national totals
clear all
***1955-1967 hand-transcriptions***
import excel "C:\Users\ot3\Documents\CRFertility\BirthData\NationalAgeRaceTranscriptions.xlsx", sheet("Sheet1") firstrow clear  //transcribed by me from print books at http://www.nber.org/vital-stats-books/
reshape long births0 births1, i( year)  j(magecat)
reshape long births, i( year magecat)  j(black)
save births_docs, replace



*1968-1975 from microdata 
use natl1968, clear
recode mrace(1=0) (nonmiss=1), gen(black) 
drop if mage8==1 | mage8==8
rename mage8 magecat
replace magecat=magecat-1
collapse (count) births=mrace, by(magecat black) //just counts birth for obs with valid maternal race. 
g year=1968
replace birth=birth*2 //all 50% samples through 1971
save births_docs_fmicrodata, replace


foreach y of numlist 1969/1971 {
di `y'
use natl`y', clear
recode mrace(1=0) (nonmiss=1), gen(black)
drop if mage8==1 | mage8==8
rename mage8 magecat
replace magecat=magecat-1
collapse (count) births=mrace, by(magecat black) //just counts birth for obs with valid maternal race. 
g year=`y'
replace birth=birth*2 //all 50% samples through 1971 
append using births_docs_fmicrodata
save births_docs_fmicrodata, replace
}

foreach y of numlist 1972/1975 {
di `y'
use natl`y', clear
recode mrace(1=0) (nonmiss=1), gen(black)
drop if mage8==1 | mage8==8
rename mage8 magecat
replace magecat=magecat-1
collapse (sum) births=recwt, by( magecat black) //summing recwt accoutns for some 50% samples, some full counts
g year=`y'
append using births_docs_fmicrodata
save births_docs_fmicrodata, replace
}

*append transcriptions and microdata 
append using births_docs
rename births births_national


***merge south and national together, calculate North as difference between national and south
merge 1:1 magecat black year using birthsbyage_south, nogen
g births_north=births_national-births_south
drop births_national
sort year black magecat
order year black magecat
save birthsbyage, replace



rm birthsbyage_south.dta
rm births_docs.dta
rm births_docs_fmicrodata.dta


***now population totals by age, race and region***

*1949
use 02896-0036-Data, clear
keep if level==2
tostring fips, format(%05.0f) replace
g fipsst=substr(fips,1,2)
destring fipsst, replace
keep fipsst wf1519 wf2024 wf2529 wf3034 wf3539 wf4044 nwf1519 nwf2024 nwf2529 nwf3034 nwf3539 nwf4044
rename wf1519 pop01
rename wf2024 pop02
rename wf2529 pop03
rename wf3034 pop04
rename wf3539 pop05
rename wf4044 pop06
rename nwf1519 pop11
rename nwf2024 pop12
rename nwf2529 pop13
rename nwf3034 pop14
rename nwf3539 pop15
rename nwf4044 pop16
reshape long pop0 pop1, i(fipsst) j(magecat)
reshape long pop, i(fipsst magecat) j(black)
g year=1949
save c1949, replace

*1959
use 02896-0039-Data, clear
keep if level==2
tostring fips, format(%05.0f) replace
g fipsst=substr(fips,1,2)
destring fipsst, replace
keep fipsst wf1519 wf2024 wf2529 wf3034 wf3539 wf4044 nwf1519 nwf2024 nwf2529 nwf3034 nwf3539 nwf4044
rename wf1519 pop01
rename wf2024 pop02
rename wf2529 pop03
rename wf3034 pop04
rename wf3539 pop05
rename wf4044 pop06
rename nwf1519 pop11
rename nwf2024 pop12
rename nwf2529 pop13
rename nwf3034 pop14
rename nwf3539 pop15
rename nwf4044 pop16
reshape long pop0 pop1, i(fipsst) j(magecat)
reshape long pop, i(fipsst magecat) j(black)
g year=1959
save c1959, replace


*1969-1975
use uswbo19agesadj, clear
label list _all
keep if year<=1975 & sex==2 
keep if age>=4 & age<=9
recode age (4=1)(5=2)(6=3)(7=4)(8=5)(9=6), gen(magecat)
destring stfips, g(fipsst)
recode race (1=0) (nonmiss=1), gen(black)
collapse (sum) pop, by(fipsst year magecat black)

*append all together
append using c1949 
append using c1959

*impute non-census years pre 1969
reshape wide pop, i(fipsst magecat black) j(year)

g annualdiff=(pop1959-pop1949)/10
g pop1955=pop1949+annualdiff*6
g pop1956=pop1949+annualdiff*7
g pop1957=pop1949+annualdiff*8
g pop1958=pop1949+annualdiff*9
drop annualdiff

g annualdiff=(pop1969-pop1959)/10
g pop1960=pop1959+annualdiff
g pop1961=pop1959+annualdiff*2
g pop1962=pop1959+annualdiff*3
g pop1963=pop1959+annualdiff*4
g pop1964=pop1959+annualdiff*5
g pop1965=pop1959+annualdiff*6
g pop1966=pop1959+annualdiff*7
g pop1967=pop1959+annualdiff*8
g pop1968=pop1959+annualdiff*9
drop annualdiff

drop pop1949
reshape long pop, i(fipsst magecat black) j(year)

*collapse to region
g south=inlist(fipsst, 1, 5, 12, 13,  22, 28, 37, 45, 47, 48, 51)
collapse (sum) pop, by(south black year magecat)
reshape wide pop, i(black year magecat) j(south)
rename pop0 pop_north
rename pop1 pop_south


save pops, replace
rm c1949.dta
rm c1959.dta




*merge birth and pop data
use pops, clear
merge 1:1 year magecat black using birthsbyage, nogen
g brate0=births_north/(pop_north/1000)
g brate1=births_south/(pop_south/1000)
reshape long brate, i(black year magecat) j(south)

keep black year magecat south brate


cd "C:\Users\ot3\Documents\CRFertility"
save frate_working_age, replace












	 
	 
